package com.ruoyi.system.service;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.RuoYiApplication;
import com.ruoyi.common.utils.CalcUtil;
import com.ruoyi.common.utils.NewDateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.UIDUtil;
import com.ruoyi.system.domain.*;
import com.ruoyi.system.uitl.ImportBasicExcelUtil;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.io.ClassPathResource;
import org.springframework.test.context.junit4.SpringRunner;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


@RunWith(SpringRunner.class)
@SpringBootTest(classes = RuoYiApplication.class)
public class ImportBasicExcelTest {
    private static final Logger logger = LoggerFactory.getLogger(ImportBasicExcelTest.class);
    static ClassPathResource classPathResource = new ClassPathResource("basicTable2020.06.08.xlsx");
//    static ClassPathResource classPathResource = new ClassPathResource("basicTableBug.xlsx");

    @Autowired
    private IAlpContractDetailService alpContractDetailService;
    @Autowired
    private IAlpContractStageService alpContractStageService;
    @Autowired
    private IAlpStageBudgetService alpStageBudgetService;
    @Autowired
    private IAlpStagePredictService alpStagePredictService;
    @Autowired
    private IAlpStageActualService alpStageActualService;
    @Autowired
    private ISysUserService iSysUserService;


    static SimpleDateFormat simpleDateFormat = new SimpleDateFormat(ImportBasicExcelUtil.timeformatter2);



    @Test
    public void delete() throws SQLException {

    }
    //导入数据入口
    @Test
    public void insert() throws SQLException {
        List<Map<String,String>> result_1 = ReadExcel();
        for (int number = 0; number < result_1.size(); number++) {
            handleEveryLine(result_1.get(number), number+1);
        }
    }

    public List<Map<String,String>> ReadExcel() {
        try{
            //读取EXCEL
            List<List<Map<String,String>>> result = ImportBasicExcelUtil.readExcelWithTitle(classPathResource);
            List<Map<String,String>> result_1 =  result.get(0);
            logger.info("************************************");
            logger.info("************excel读取成功************");
            logger.info("************************************");
            return result_1;
        }catch(Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public void handleEveryLine(Map<String, String> map, int number) {
        try{
            if(StringUtils.isEmpty(map.get("项目编号"))){
                logger.error("第{}行插入失败，项目编号为空，整行内容：{}", number+1, JSON.toJSONString(map));
                return;
            }
            if(StringUtils.isEmpty(map.get("合同总额"))){
                logger.error("第{}行插入失败，合同总额为空，项目编号为:{}，整行内容：{}", number+1, map.get("项目编号"), JSON.toJSONString(map));
                return;
            }
            if(StringUtils.isEmpty(map.get("责任人"))){
                logger.error("第{}行插入失败，责任人为空，项目编号为:{}，整行内容：{}", number+1, map.get("项目编号"), JSON.toJSONString(map));
                return;
            }
            SysUser user = iSysUserService.selectUserByLoginName(map.get("责任人"));
            if (null == user) {
                logger.error("第{}行插入失败，未查到该用户，项目编号为:{}，整行内容：{}", number+1, map.get("项目编号"), JSON.toJSONString(map));
                return;
            }
            AlpContractDetail alpContractDetail = new AlpContractDetail();
            alpContractDetail.setContractId(Long.valueOf(map.get("序号")));
            alpContractDetail.setContractNo(map.get("项目编号").trim());
            alpContractDetail.setContractName(map.get("项目名称").trim());

            //部门
            alpContractDetail.setXsDepartment(map.get("行业"));//疑问点  行业 和 销售二级部门
            alpContractDetail.setIndustry(map.get("行业"));
            alpContractDetail.setDeptId(user.getDeptId());

            alpContractDetail.setXsPrincipalId(user.getUserId().toString());
            alpContractDetail.setUserId(user.getUserId());
            alpContractDetail.setXsPrincipalName(map.get("责任人"));
            SysUser user2 = iSysUserService.selectUserByLoginName(map.get("第二责任人"));
            if (null != user2) {
                alpContractDetail.setXsPrincipal2Id(user2.getUserId().toString());
                alpContractDetail.setXsPrincipal2Name(map.get("第二责任人"));
            }
            alpContractDetail.setJfPrincipalName(map.get("项目经理")); //交付负责人

            alpContractDetail.setPartyAName(map.get("客户名称"));
            alpContractDetail.setPayee(map.get("收款单位"));
            alpContractDetail.setBooking(new BigDecimal(map.get("合同总额")));
            alpContractDetail.setDelFlag("0");
            if (StringUtils.isNotEmpty(map.get("20年期初应收合同余额"))) {
                alpContractDetail.setBalanceSince2020(new BigDecimal(map.get("20年期初应收合同余额")));
            }
//            alpContractDetail.setBalance(new BigDecimal(map.get("最新应收合同余额")));

            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(ImportBasicExcelUtil.timeformatter);
            if (StringUtils.isNotEmpty(map.get("中标时间"))) {
                Date biddingTime = simpleDateFormat.parse(map.get("中标时间"));
                alpContractDetail.setBiddingTime(biddingTime);
                String year = NewDateUtils.format(biddingTime, NewDateUtils.YYYY);
                alpContractDetail.setBiddingInyear(Integer.valueOf(year));
            }
            if (StringUtils.isNotEmpty(map.get("签订合同时间"))) {
                Date contractSignTime = simpleDateFormat.parse(map.get("签订合同时间"));
                alpContractDetail.setContractSignTime(contractSignTime);
            }
            if (StringUtils.isNotEmpty(map.get("一级部门"))) {
                alpContractDetail.setJfDepartment(map.get("一级部门"));
            }
            if (StringUtils.isNotEmpty(map.get("二级部门"))) {
                alpContractDetail.setJfDepartment2(map.get("二级部门"));
            }
            if (StringUtils.isNotEmpty(map.get("bidding年度"))) {
                alpContractDetail.setContractSignedInyear(map.get("bidding年度"));
            }

            if (StringUtils.isNotEmpty(map.get("初验时间"))) {
                Date firstTime = simpleDateFormat.parse(map.get("初验时间"));
                alpContractDetail.setFirstAcceptanceTime(firstTime);
            }

            if (StringUtils.isNotEmpty(map.get("终验时间"))) {
                Date finalTime = simpleDateFormat.parse(map.get("终验时间"));
                alpContractDetail.setFinialAcceptanceTime(finalTime);
            }
            if (StringUtils.isNotEmpty(map.get("回款条件"))) {
                alpContractDetail.setPaymentCondition(map.get("回款条件"));
            }


            //收集阶段表
            processStage(alpContractDetail, map.get("阶段详情"));
            //收集预算表
            processBudget(alpContractDetail, map.get("预算"));
            //收集预测表
            processPredict(alpContractDetail, map.get("预测"));
            //收集实际表
            processActual(alpContractDetail, map.get("实际"));

            //插入数据库
            insertIntoDB(alpContractDetail);

            logger.info("第{}行插入成功，项目编号为:{}，整行内容：{}", number+1, map.get("项目编号"), JSON.toJSONString(map));
        }catch(Exception e){
            e.printStackTrace();
            logger.error("第{}行异常，项目编号为:{}，整行内容：{}", number+1, map.get("项目编号"), JSON.toJSONString(map));
        }finally{

        }
    }

    private void insertIntoDB(AlpContractDetail alpContractDetail) {
        alpContractDetailService.insertAlpContractDetail(alpContractDetail);
        List<AlpContractStage> stageList = alpContractDetail.getAlpContractStages();
        for (AlpContractStage stage : stageList) {
            alpContractStageService.insertAlpContractStage(stage);
            if (null != stage.getStageBudgetList()) {
                for (AlpStageBudget budget :stage.getStageBudgetList()) {
                    alpStageBudgetService.insertAlpStageBudget(budget);
                }
            }
            if (null != stage.getStagePredictList()) {
                for (AlpStagePredict predict :stage.getStagePredictList()) {
                    alpStagePredictService.insertAlpStagePredict(predict);
                }
            }
            if (null != stage.getStageActualList()) {
                for (AlpStageActual actual :stage.getStageActualList()) {
                    alpStageActualService.insertAlpStageActual(actual);
                }
            }

        }


    }


    private void processYiHuiKuanStage(AlpContractDetail alpContractDetail) {
        //已回款 特殊处理
        //根据合同总额和余额 比较  有差异的   补充已回款阶段
        if (null == alpContractDetail.getBiddingTime()) {
            logger.error("没有合同签订时间，BiddingTime is null");
            return;
        }
        Date bcompareDate = new Date();
        try {
            bcompareDate = simpleDateFormat.parse("2020-01-01");
        } catch (ParseException e) {
            e.printStackTrace();
        }

        if (alpContractDetail.getBiddingTime().after(bcompareDate)) {
            return;
        }
        if (!alpContractDetail.getBooking().equals(alpContractDetail.getBalanceSince2020())) {
            AlpContractStage stage = new AlpContractStage();
            stage.setStageId(UIDUtil.nextId());
            stage.setContractId(alpContractDetail.getContractId());
            stage.setStageName("已回款");
            BigDecimal reversedMoney = alpContractDetail.getBooking().subtract(alpContractDetail.getBalanceSince2020());
            stage.setStageMoney(reversedMoney);
            try {
                Date end2019Time = simpleDateFormat.parse("2019-12-31");
                stage.setContractEndTime(end2019Time);
                stage.setPredictEndTime(end2019Time);
                stage.setActualEndTime(end2019Time);

                AlpStageBudget budget = new AlpStageBudget();
                AlpStagePredict predict = new AlpStagePredict();
                AlpStageActual actual = new AlpStageActual();
                budget.setId(UIDUtil.nextId());
                budget.setContractId(alpContractDetail.getContractId());
                budget.setStageId(stage.getStageId());
                budget.setBudgetMoney(reversedMoney);
                budget.setBudgetTime(end2019Time);
                budget.setDelFlag(0);
                predict.setId(UIDUtil.nextId());
                predict.setContractId(alpContractDetail.getContractId());
                predict.setStageId(stage.getStageId());
                predict.setPredictMoney(reversedMoney);
                predict.setPredictTime(end2019Time);
                predict.setDelFlag(0);
                actual.setId(UIDUtil.nextId());
                actual.setContractId(alpContractDetail.getContractId());
                actual.setStageId(stage.getStageId());
                actual.setActualMoney(reversedMoney);
                actual.setActualTime(end2019Time);
                actual.setDelFlag(0);
                List<AlpStageBudget> stageBudgetList = new ArrayList<>();
                List<AlpStagePredict> stagePredictList = new ArrayList<>();
                List<AlpStageActual> stageActualList = new ArrayList<>();
                stageBudgetList.add(budget);
                stagePredictList.add(predict);
                stageActualList.add(actual);
                stage.setStageBudgetList(stageBudgetList);
                stage.setStagePredictList(stagePredictList);
                stage.setStageActualList(stageActualList);
            } catch (ParseException e) {
                e.printStackTrace();
            }

            List<AlpContractStage> stageList = new ArrayList<>();
            stageList.add(stage);
            alpContractDetail.setAlpContractStages(stageList);
        }
    }
    private void processStage(AlpContractDetail alpContractDetail, String stageDetail) {
        processYiHuiKuanStage(alpContractDetail);
        JSONArray jsonArray = JSONArray.parseArray(stageDetail);
        if (null == jsonArray) {
            return;
        }
        jsonArray.stream().forEach(jsonObejct->eachStage(alpContractDetail,(JSONObject) jsonObejct));
    }

    private void processBudget(AlpContractDetail alpContractDetail, String budgetDetail) {
        JSONArray jsonArray = JSONArray.parseArray(budgetDetail);
        if (null == jsonArray) {
            return;
        }
        jsonArray.stream().forEach(jsonObejct->eachBudget(alpContractDetail,(JSONObject) jsonObejct));
    }

    private void processPredict(AlpContractDetail alpContractDetail, String predictDetail) {
        JSONArray jsonArray = JSONArray.parseArray(predictDetail);
        if (null == jsonArray) {
            return;
        }
        jsonArray.stream().forEach(jsonObejct->eachPredict(alpContractDetail,(JSONObject) jsonObejct));
    }

    private void processActual(AlpContractDetail alpContractDetail, String actualDetail) {
        JSONArray jsonArray = JSONArray.parseArray(actualDetail);
        if (null == jsonArray) {
            return;
        }
        jsonArray.stream().forEach(jsonObejct->eachActual(alpContractDetail,(JSONObject) jsonObejct));
    }

    /*
        [
         {
          "阶段名称": "验收",
          "回款比例": "90%",
          "回款金额": "452880.00",
          "阶段所属部门": "交付部",
          "合同约定时间": "2019-09-02",
          "预测时间": "2019-09-02",
          "实际时间": "2019-09-02"
         },
         {
          "阶段名称": "验收满两年",
          "回款比例": "10%",
          "回款金额": "50320.00",
          "阶段所属部门": "运维部",
          "合同约定时间": "2021-09-02",
          "预测时间": "2021-09-02",
          "实际时间": ""
         }
        ]
    * */
    private void eachStage(AlpContractDetail alpContractDetail, JSONObject jsonObject) {

        AlpContractStage stage = new AlpContractStage();
        stage.setStageId(UIDUtil.nextId());
        stage.setContractId(alpContractDetail.getContractId());
        stage.setStageName(jsonObject.getString("阶段名称"));
        stage.setStageDepartment(jsonObject.getString("阶段所属部门"));
        try {
            if (StringUtils.isNotEmpty(jsonObject.getString("合同约定时间"))) {
                Date contractTime = simpleDateFormat.parse(jsonObject.getString("合同约定时间"));
                stage.setContractEndTime(contractTime);
            }
            if (StringUtils.isNotEmpty(jsonObject.getString("预测时间"))) {
                Date predictEndTime = simpleDateFormat.parse(jsonObject.getString("预测时间"));
                stage.setPredictEndTime(predictEndTime);
            }
            if (StringUtils.isNotEmpty(jsonObject.getString("实际时间"))) {
                Date actualEndTime = simpleDateFormat.parse(jsonObject.getString("实际时间"));
                stage.setActualEndTime(actualEndTime);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }

        //stagemoney
        BigDecimal res = calcHuiKuanMoney(alpContractDetail.getBooking(), jsonObject);
        if (null != res) {
            stage.setStageMoney(res);
        }

        if (null == alpContractDetail.getAlpContractStages()){
            List<AlpContractStage> stageList = new ArrayList<>();
            stageList.add(stage);
            alpContractDetail.setAlpContractStages(stageList);
        } else {
            alpContractDetail.getAlpContractStages().add(stage);
        }
    }

    /*
    [
     {
      "阶段名称": "验收",
      "时间": "2020-02-29",
      "回款比例": "90%",
      "回款金额": "452880.00"
     },
     {
      "阶段名称": "验收满两年",
      "时间": "2022-10-31",
      "回款比例": "10%",
      "回款金额": "50320.00"
     }
    ]*/
    private void eachBudget(AlpContractDetail alpContractDetail, JSONObject jsonObject) {
        if (StringUtils.isEmpty(jsonObject.getString("阶段名称"))) {
            return;
        }
        AlpStageBudget budget = new AlpStageBudget();
        budget.setDelFlag(0);
        //budgetMoney
        BigDecimal res = calcHuiKuanMoney(alpContractDetail.getBooking(), jsonObject);
        if (null != res) {
            budget.setBudgetMoney(res);
        }

        Date time = null;
        try {
            Set<String> keyset = jsonObject.keySet();
            String keyTime = "时间";
            for (String s: keyset) {
                if (s.contains("时间")) {
                    keyTime = s;
                }
            }
            if (StringUtils.isNotEmpty(jsonObject.getString(keyTime))) {
                time = simpleDateFormat.parse(jsonObject.getString(keyTime));
                budget.setBudgetTime(time);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }

        List<AlpContractStage> stageList = alpContractDetail.getAlpContractStages();
        for (AlpContractStage stage : stageList) {
            if(jsonObject.getString("阶段名称").equals(stage.getStageName())) {
                budget.setId(UIDUtil.nextId());
                budget.setContractId(alpContractDetail.getContractId());
                budget.setStageId(stage.getStageId());
                if (null == stage.getStageBudgetList()) {
                    List<AlpStageBudget> stageBudgets = new ArrayList<>();
                    stageBudgets.add(budget);
                    stage.setStageBudgetList(stageBudgets);
                } else {
                    stage.getStageBudgetList().add(budget);
                }

            }
        }
    }

    private void eachPredict(AlpContractDetail alpContractDetail, JSONObject jsonObject) {
        if (StringUtils.isEmpty(jsonObject.getString("阶段名称"))) {
            return;
        }
        AlpStagePredict predict = new AlpStagePredict();
        predict.setDelFlag(0);
        //budgetMoney
        BigDecimal res = calcHuiKuanMoney(alpContractDetail.getBooking(), jsonObject);
        if (null != res) {
            predict.setPredictMoney(res);
        }

        Date time = null;
        try {
            Set<String> keyset = jsonObject.keySet();
            String keyTime = "时间";
            for (String s: keyset) {
                if (s.contains("时间")) {
                    keyTime = s;
                }
            }
            if (StringUtils.isNotEmpty(jsonObject.getString(keyTime))) {
                time = simpleDateFormat.parse(jsonObject.getString(keyTime));
                predict.setPredictTime(time);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }

        List<AlpContractStage> stageList = alpContractDetail.getAlpContractStages();
        for (AlpContractStage stage : stageList) {
            if(jsonObject.getString("阶段名称").equals(stage.getStageName())) {
                predict.setId(UIDUtil.nextId());
                predict.setContractId(alpContractDetail.getContractId());
                predict.setStageId(stage.getStageId());
                if (null == stage.getStagePredictList()) {
                    List<AlpStagePredict> stagePredicts = new ArrayList<>();
                    stagePredicts.add(predict);
                    stage.setStagePredictList(stagePredicts);
                } else {
                    stage.getStagePredictList().add(predict);
                }
            }
        }
    }
    /*
    [
     {
      "阶段名称": "验收",
      "时间": "2020-01-08",
      "回款比例": "100%"
     }
    ]*/
    private void eachActual(AlpContractDetail alpContractDetail, JSONObject jsonObject) {
        if (StringUtils.isEmpty(jsonObject.getString("阶段名称"))) {
            return;
        }
        AlpStageActual actual = new AlpStageActual();
        actual.setDelFlag(0);
        BigDecimal res = calcHuiKuanMoney(alpContractDetail.getBooking(), jsonObject);
        if (null != res) {
            actual.setActualMoney(res);
        }
        Date time = null;
        try {
            Set<String> keyset = jsonObject.keySet();
            String keyTime = "时间";
            for (String s: keyset) {
                if (s.contains("时间")) {
                    keyTime = s;
                }
            }
            if (StringUtils.isNotEmpty(jsonObject.getString(keyTime))) {
                time = simpleDateFormat.parse(jsonObject.getString(keyTime));
                actual.setActualTime(time);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        List<AlpContractStage> stageList = alpContractDetail.getAlpContractStages();
        for (AlpContractStage stage : stageList) {
            if(jsonObject.getString("阶段名称").equals(stage.getStageName())) {
                actual.setId(UIDUtil.nextId());
                actual.setContractId(alpContractDetail.getContractId());
                actual.setStageId(stage.getStageId());
                if (null == stage.getStageActualList()) {
                    List<AlpStageActual> stageActuals = new ArrayList<>();
                    stageActuals.add(actual);
                    stage.setStageActualList(stageActuals);
                } else {
                    stage.getStageActualList().add(actual);
                }
            }
        }
    }

    private BigDecimal calcHuiKuanMoney(BigDecimal booking, JSONObject jsonObject) {
        if (StringUtils.isNotEmpty(jsonObject.getString("回款金额"))) {
            return new BigDecimal(jsonObject.getString("回款金额"));
        } else if (StringUtils.isNotEmpty(jsonObject.getString("回款比例"))) {
            if (booking != null) {
                return CalcUtil.calcPercentage(booking, jsonObject.getString("回款比例"));
            }
        }
        return null;
    }

    @Test
    public void calcTestHuiKuanMoney() {
        BigDecimal booking = new BigDecimal("1403292.5");
        System.out.println(CalcUtil.calcPercentage(booking, "80%"));
    }
}
